package top.aniss.spark.beifeng.jdbc;

import org.apache.log4j.Logger;
import top.aniss.spark.beifeng.common.Const;
import top.aniss.spark.beifeng.conf.ConfigurationManager;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;

/**
 * @author Andre Wei
 * create 2018-07-11 下午1:23
 */
public class JDBCHelper {
    private static Logger logger = Logger.getLogger(JDBCHelper.class);

    static {
        try {
            String driver = ConfigurationManager.getProperty(Const.JDBC.DRIVER);
            Class.forName(driver);
        } catch (ClassNotFoundException e) {
            logger.error(e);
        }
    }


    private static JDBCHelper instance = null;

    private LinkedList<Connection> datasource = new LinkedList<>();

    public static JDBCHelper getInstance() {
        if (instance == null) {
            synchronized (JDBCHelper.class) {
                if (instance == null) {
                    instance = new JDBCHelper();
                }
            }
        }
        return instance;
    }

    private JDBCHelper() {
        int datasourceSize = ConfigurationManager.getInteger(Const.JDBC.DATASOURCE_SIZE);

        for (int i = 0; i < datasourceSize; i++) {
            String url = ConfigurationManager.getProperty(Const.JDBC.URL);
            String username = ConfigurationManager.getProperty(Const.JDBC.USERNAME);
            String password = ConfigurationManager.getProperty(Const.JDBC.PASSWORD);
            try {
                Connection conn = DriverManager.getConnection(url, username, password);
                datasource.push(conn);
            } catch (SQLException e) {
                logger.error("无法建立连接", e);
            }
        }
    }


    public synchronized Connection getConnection() {
        while (datasource.size() == 0) {
            try {
                Thread.sleep(10);
            } catch (InterruptedException e) {
                e.printStackTrace();
            }
        }
        return datasource.poll();
    }


    public int executeUpdate(String sql, Object[] params) {
        int rnt = 0;
        Connection connection = null;
        PreparedStatement preparedStatement = null;

        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                Object str = params[i];
                preparedStatement.setObject(i + 1, params[i]);
            }
            rnt = preparedStatement.executeUpdate();
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            if (connection != null) {
                datasource.push(connection);
            }
        }
        return rnt;
    }

    public void executeQuery(String sql, Object[] params, QueryCallback callback) {
        Connection connection = null;
        PreparedStatement preparedStatement;
        ResultSet resultSet;

        try {
            connection = getConnection();
            preparedStatement = connection.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
                preparedStatement.setObject(i + 1, params[i]);
            }
            resultSet = preparedStatement.executeQuery();
            callback.process(resultSet);
        } catch (SQLException e) {
            logger.error(e);
        } finally {
            if (connection != null) {
                datasource.push(connection);
            }
        }
    }

    /**
     * 批量执行SQL语句
     * <p>
     * 批量执行SQL语句，是JDBC中的一个高级功能
     * 默认情况下，每次执行一条SQL语句，就会通过网络连接，向MySQL发送一次请求
     * <p>
     * 但是，如果在短时间内要执行多条结构完全一模一样的SQL，只是参数不同
     * 虽然使用PreparedStatement这种方式，可以只编译一次SQL，提高性能，但是，还是对于每次SQL
     * 都要向MySQL发送一次网络请求
     * <p>
     * 可以通过批量执行SQL语句的功能优化这个性能
     * 一次性通过PreparedStatement发送多条SQL语句，比如100条、1000条，甚至上万条
     * 执行的时候，也仅仅编译一次就可以
     * 这种批量执行SQL语句的方式，可以大大提升性能
     *
     * @param sql        sql
     * @param paramsList 参数
     * @return 每条SQL语句影响的行数
     */
    public int[] executeBatch(String sql, List<Object[]> paramsList) {
        int[] rtn = null;
        Connection connection = null;
        PreparedStatement pstmt = null;
        try {
            connection = getConnection();
            // 1st: 取消自动提交
            connection.setAutoCommit(false);
            pstmt = connection.prepareStatement(sql);
            // 2nd: 使用prepareStatement.addBatch()方法加入批量的SQL
            for (Object[] params : paramsList) {
                for (int i = 0; i < params.length; i++) {
                    pstmt.setObject(i + 1, params[i]);
                }
                pstmt.addBatch();
            }

            // 3rd: 使用prepareStatement.executeBatch()方法执行SQL
            rtn = pstmt.executeBatch();

            // last: 使用Connection对象提交SQL语句
            connection.commit();

        } catch (SQLException e) {
            logger.error(e);
        }finally {
            if (connection != null) {
                datasource.push(connection);
            }
        }


        return rtn;
    }

    /**
     * 内部回调接口
     */
    public interface QueryCallback {
        void process(ResultSet resultSet) throws SQLException;
    }


}
